from sqlalchemy import create_engine
import tushare as ts
import talib
import numpy as np
import pandas as pd
import psycopg2 as pg
from datetime import timedelta, date

def handdata():
	#从数据库里查日期和code
	# 连接数据库
    try:
        conn = pg.connect(dbname='postgres', host='127.0.0.1', user='lengdh', password='123456',
                   port='5432')
        cur = conn.cursor()
        cur.execute(
           "select date,code  from basic_data  where ma20 is  null order by date desc") ##跑历史
           #"select date,code  from basic_data  where  to_date(date,'YYYY-MM-DD')='2017-12-14'") ##跑当天
        rows = cur.fetchall()
        #rows=[['2017-12-07','002828']]
        i=0
        for r in rows:
            #print(r)
            i=i+1
            print(i)
            #df = ts.get_k_data(r[1], ktype='D' , start='2016-06-15', end=r[0])
            #print(df)
            #print('==============')
            cur.execute("select close  from tick_d_data where code = %s and to_date(date,'YYYY-MM-DD') <= %s order by date ",(r[1],r[0]))#价格一定要按日期排序，否则macd不一致
            prices=cur.fetchall()
            ma20=MA(np.array(prices).flatten(), timeperiod=20, matype=0)
            ma10=MA(np.array(prices).flatten(), timeperiod=10, matype=0)
            ma5=MA(np.array(prices).flatten(), timeperiod=5, matype=0)
            ma13=MA(np.array(prices).flatten(), timeperiod=13, matype=0)
            ma34=MA(np.array(prices).flatten(), timeperiod=34, matype=0)
            ma55=MA(np.array(prices).flatten(), timeperiod=55, matype=0)
            
            #计算完了ma要写到数据
            cur.execute("update basic_data set ma20 = %s where date = %s and code = %s",(ma20,r[0],r[1]))
            cur.execute("update basic_data set ma10 = %s where date = %s and code = %s",(ma10,r[0],r[1]))
            cur.execute("update basic_data set ma5 = %s where date = %s and code = %s",(ma5,r[0],r[1]))
            cur.execute("update basic_data set ma13 = %s where date = %s and code = %s",(ma13,r[0],r[1]))
            cur.execute("update basic_data set ma34 = %s where date = %s and code = %s",(ma34,r[0],r[1]))
            cur.execute("update basic_data set ma55 = %s where date = %s and code = %s",(ma55,r[0],r[1]))
            conn.commit()
    except Exception as e:
        print ('Error: ' + e)
    print("Done=======")
    cur.close()
    conn.close()
# 定义MA函数  
def MA(prices, timeperiod=20, matype=0):
    ma = talib.MA(prices, timeperiod, matype=0)
    return round(ma[-1],2)
if __name__ == "__main__":
	handdata()